ALTER TABLE Setting ADD UNIQUE (Name) GO ALTER TABLE [SystemCenter.Setting] ADD UNIQUE (Name) GO ALTER TABLE [MiMD.Setting] ADD UNIQUE (Name) GO DECLARE @DF_FileGroup_Error NVARCHAR(100) SELECT @DF_FileGroup_Error = CONCAT ( 'ALTER TABLE FileGroup DROP CONSTRAINT ', ( SELECT sys.default_constraints.name FROM sys.default_constraints JOIN sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id WHERE sys.default_constraints.parent_object_id = object_id('FileGroup') AND sys.columns.name = 'Error' ) ) EXECUTE sp_executesql @DF_FileGroup_Error GO ALTER TABLE FileGroup DROP COLUMN Error GO ALTER TABLE FileGroup ADD ProcessingStatus INT NOT NULL DEFAULT(0) GO ALTER TABLE ChannelTemplateFile ADD ShowTrend BIT NOT NULL DEFAULT(1), ShowEvents BIT NOT NULL DEFAULT(1), SortOrder INT NOT NULL DEFAULT(0) GO INSERT INTO NodeType VALUES('Grafana', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.Grafana.GrafanaHostingNode') GO ALTER TABLE Node ADD AssignedHostRegistrationID INT NULL REFERENCES HostRegistration(ID) GO CREATE TABLE GenerationAttributes ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, AssetID INT NOT NULL REFERENCES Asset(ID), ) GO CREATE TABLE StationAuxAttributes ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, AssetID INT NOT NULL REFERENCES Asset(ID), ) GO CREATE TABLE StationBatteryAttributes ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, AssetID INT NOT NULL REFERENCES Asset(ID), ) GO -- Generation Model CREATE VIEW Generation AS SELECT AssetID AS ID, AssetKey, VoltageKV, Description, AssetName, AssetTypeID, Spare FROM Asset JOIN GenerationAttributes ON Asset.ID = GenerationAttributes.AssetID GO CREATE TRIGGER TR_INSERT_Generation ON GENERATION INSTEAD OF INSERT AS BEGIN INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare) SELECT AssetKey AS AssetKey, (SELECT ID FROM AssetType WHERE Name = 'Generation') AS AssetTypeID, Description AS Description, AssetName AS AssetName, VoltageKV AS VoltageKV, Spare AS Spare FROM INSERTED INSERT INTO GenerationAttributes (AssetID) SELECT (SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID FROM INSERTED END GO CREATE TRIGGER TR_UPDATE_Generation ON GENERATION INSTEAD OF UPDATE AS BEGIN IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare)) BEGIN UPDATE Asset SET Asset.AssetKey = INSERTED.AssetKey, Asset.Description = INSERTED.Description, Asset.AssetName = INSERTED.AssetName, Asset.VoltageKV = INSERTED.VoltageKV, Asset.Spare = INSERTED.Spare FROM ASSET INNER JOIN INSERTED ON INSERTED.ID = ASSET.ID; END END GO -- END Generation Model Triggers -- Station Auxilary Model CREATE VIEW StationAux AS SELECT AssetID AS ID, AssetKey, VoltageKV, Description, AssetName, AssetTypeID, Spare FROM Asset JOIN StationAuxAttributes ON Asset.ID = StationAuxAttributes.AssetID GO CREATE TRIGGER TR_INSERT_Aux ON STATIONAUX INSTEAD OF INSERT AS BEGIN INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare) SELECT AssetKey AS AssetKey, (SELECT ID FROM AssetType WHERE Name = 'StationAux') AS AssetTypeID, Description AS Description, AssetName AS AssetName, VoltageKV AS VoltageKV, Spare AS Spare FROM INSERTED INSERT INTO StationAuxAttributes (AssetID) SELECT (SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID FROM INSERTED END GO CREATE TRIGGER TR_UPDATE_Aux ON STATIONAUX INSTEAD OF UPDATE AS BEGIN IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare)) BEGIN UPDATE Asset SET Asset.AssetKey = INSERTED.AssetKey, Asset.Description = INSERTED.Description, Asset.AssetName = INSERTED.AssetName, Asset.VoltageKV = INSERTED.VoltageKV, Asset.Spare = INSERTED.Spare FROM ASSET INNER JOIN INSERTED ON INSERTED.ID = ASSET.ID; END END GO -- END Station Auxilary Model Triggers -- Station Battery Model CREATE VIEW StationBattery AS SELECT AssetID AS ID, AssetKey, VoltageKV, Description, AssetName, AssetTypeID, Spare FROM Asset JOIN StationBatteryAttributes ON Asset.ID = StationBatteryAttributes.AssetID GO CREATE TRIGGER TR_INSERT_Battery ON STATIONBATTERY INSTEAD OF INSERT AS BEGIN INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare) SELECT AssetKey AS AssetKey, (SELECT ID FROM AssetType WHERE Name = 'StationBattery') AS AssetTypeID, Description AS Description, AssetName AS AssetName, VoltageKV AS VoltageKV, Spare AS Spare FROM INSERTED INSERT INTO StationBatteryAttributes (AssetID) SELECT (SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID FROM INSERTED END GO CREATE TRIGGER TR_UPDATE_Battery ON STATIONBATTERY INSTEAD OF UPDATE AS BEGIN IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare)) BEGIN UPDATE Asset SET Asset.AssetKey = INSERTED.AssetKey, Asset.Description = INSERTED.Description, Asset.AssetName = INSERTED.AssetName, Asset.VoltageKV = INSERTED.VoltageKV, Asset.Spare = INSERTED.Spare FROM ASSET INNER JOIN INSERTED ON INSERTED.ID = ASSET.ID; END END GO -- END Station Bettery Model Triggers DECLARE @FK_PQMeasurement_UnitID NVARCHAR(100) SELECT @FK_PQMeasurement_UnitID = CONCAT ( 'ALTER TABLE PQMeasurement DROP CONSTRAINT ', ( SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE COLUMN_NAME = 'UnitID' AND TABLE_NAME = 'PQMeasurement' ) ) EXECUTE sp_executesql @FK_PQMeasurement_UnitID GO DECLARE @FK_PQMeasurement_PhaseID NVARCHAR(100) SELECT @FK_PQMeasurement_PhaseID = CONCAT ( 'ALTER TABLE PQMeasurement DROP CONSTRAINT ', ( SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE COLUMN_NAME = 'PhaseID' AND TABLE_NAME = 'PQMeasurement' ) ) EXECUTE sp_executesql @FK_PQMeasurement_PhaseID GO DECLARE @DF_PQMeas_HG NVARCHAR(100) SELECT @DF_PQMeas_HG = CONCAT ( 'ALTER TABLE PQMeasurement DROP CONSTRAINT ', ( SELECT sys.default_constraints.name FROM sys.default_constraints JOIN sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id WHERE sys.default_constraints.parent_object_id = object_id('PQMeasurement') AND sys.columns.name = 'HarmonicGroup' ) ) EXECUTE sp_executesql @DF_PQMeas_HG GO DECLARE @DF_PQMeas_Enabled NVARCHAR(100) SELECT @DF_PQMeas_Enabled = CONCAT ( 'ALTER TABLE PQMeasurement DROP CONSTRAINT ', ( SELECT sys.default_constraints.name FROM sys.default_constraints JOIN sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id WHERE sys.default_constraints.parent_object_id = object_id('PQMeasurement') AND sys.columns.name = 'Enabled' ) ) EXECUTE sp_executesql @DF_PQMeas_Enabled GO --Separated these out since GPC had some unanticipated default constraints to deal with individually and it makes it easier to ID them if they're separate queries. ALTER TABLE PQMeasurement DROP COLUMN UnitID GO ALTER TABLE PQMeasurement DROP COLUMN PhaseID GO ALTER TABLE PQMeasurement DROP COLUMN HarmonicGroup GO ALTER TABLE PQMeasurement DROP COLUMN Enabled GO ALTER TABLE PQMeasurement ADD Unit VARCHAR(200) NOT NULL DEFAULT('None') GO DROP TABLE Unit GO DROP TABLE PQTrendStat GO DROP TABLE StepChangeStat GO DROP TABLE StepChangeMeasurement GO CREATE NONCLUSTERED INDEX IX_PQMeasurement_MeasurementTypeID ON PQMeasurement(MeasurementTypeID ASC) GO CREATE NONCLUSTERED INDEX IX_PQMeasurement_MeasurementCharacteristicID ON PQMeasurement(MeasurementCharacteristicID ASC) GO CREATE TABLE MeterDependentAssetDesignation ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, MeterAssetID INT NOT NULL REFERENCES MeterAsset(ID), Designation VARCHAR(200) NOT NULL ) GO CREATE NONCLUSTERED INDEX IX_MeterDependentAssetDesignation_MeterAssetID ON MeterDependentAssetDesignation(MeterAssetID ASC) GO CREATE NONCLUSTERED INDEX IX_FaultDetectionLogic_MeterAssetID ON FaultDetectionLogic(MeterAssetID ASC) GO ALTER TABLE AlarmLog ADD SeverityID INT NOT NULL REFERENCES AlarmSeverity(ID) DEFAULT(4) GO CREATE TABLE LatestAlarmLog ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, AlarmID INT NOT NULL UNIQUE REFERENCES Alarm(ID), AlarmLogID INT NOT NULL REFERENCES AlarmLog(ID), SeverityID INT NOT NULL REFERENCES AlarmSeverity(ID), StartTime DATETIME NOT NULL, EndTime DATETIME NULL ) GO CREATE NONCLUSTERED INDEX IX_AlarmGroup_AlarmTypeID ON AlarmGroup(AlarmTypeID ASC) GO CREATE NONCLUSTERED INDEX IX_AlarmGroup_SeverityID ON AlarmGroup(SeverityID ASC) GO CREATE NONCLUSTERED INDEX IX_Alarm_AlarmGroupID ON Alarm(AlarmGroupID ASC) GO CREATE NONCLUSTERED INDEX IX_Alarm_SeriesID ON Alarm(SeriesID ASC) GO CREATE NONCLUSTERED INDEX IX_AlarmFactor_AlarmGroupID ON AlarmFactor(AlarmGroupID ASC) GO CREATE NONCLUSTERED INDEX IX_AlarmFactor_SeverityID ON AlarmFactor(SeverityID ASC) GO CREATE NONCLUSTERED INDEX IX_AlarmDayGroupAlarmDay_AlarmDayID ON AlarmDayGroupAlarmDay(AlarmDayID ASC) GO CREATE NONCLUSTERED INDEX IX_AlarmDayGroupAlarmDay_AlarmDayGroupID ON AlarmDayGroupAlarmDay(AlarmDayGroupID ASC) GO CREATE NONCLUSTERED INDEX IX_LatestAlarmLog_AlarmID ON LatestAlarmLog(AlarmID ASC) GO CREATE NONCLUSTERED INDEX IX_LatestAlarmLog_SeverityID ON LatestAlarmLog(SeverityID ASC) GO CREATE TRIGGER AlarmLog_UpdateLatestAlarmLog ON AlarmLog AFTER INSERT AS BEGIN SET NOCOUNT ON; -- Update existing records in LatestAlarmLog UPDATE LatestAlarmLog SET AlarmLogID = inserted.ID, SeverityID = inserted.SeverityID, StartTime = inserted.StartTime, EndTime = inserted.EndTime FROM LatestAlarmLog JOIN inserted ON inserted.AlarmID = LatestAlarmLog.AlarmID JOIN AlarmLog ON LatestAlarmLog.AlarmLogID = AlarmLog.ID WHERE inserted.StartTime >= AlarmLog.StartTime -- Determine which alarms have never had logs before now SELECT DISTINCT inserted.AlarmID INTO #alarm FROM inserted LEFT OUTER JOIN LatestAlarmLog ON inserted.AlarmID = LatestAlarmLog.AlarmID WHERE LatestAlarmLog.ID IS NULL -- Insert new records in LatestAlarmLog -- for alarms that never had logs before now INSERT INTO LatestAlarmLog(AlarmID, AlarmLogID, SeverityID, StartTime, EndTime) SELECT AlarmLog.AlarmID, AlarmLog.ID AlarmLogID, AlarmLog.SeverityID, AlarmLog.StartTime, AlarmLog.EndTime FROM #alarm CROSS APPLY ( SELECT TOP 1 * FROM inserted WHERE AlarmID = #alarm.AlarmID ORDER BY StartTime DESC, ID DESC ) AlarmLog END GO DROP VIEW AlarmGroupView GO CREATE VIEW AlarmGroupView AS SELECT AlarmGroup.ID, AlarmGroup.Name, AlarmSeverity.Name AlarmSeverity, CountStats.ChannelCount Channels, CountStats.MeterCount Meters, LastAlarm.StartTime LastAlarmStart, LastAlarm.EndTime LastAlarmEnd, LastAlarm.ChannelName LastChannel, LastAlarm.MeterName LastMeter, AlarmType.Description AS AlarmType FROM AlarmGroup LEFT JOIN AlarmSeverity ON AlarmGroup.SeverityID = AlarmSeverity.ID LEFT JOIN AlarmType ON AlarmGroup.AlarmTypeID = AlarmType.ID OUTER APPLY ( SELECT COUNT(DISTINCT Channel.ID) ChannelCount, COUNT(DISTINCT Channel.MeterID) MeterCount FROM Channel JOIN Series ON Series.ChannelID = Channel.ID JOIN Alarm ON Alarm.SeriesID = Series.ID WHERE Alarm.AlarmGroupID = AlarmGroup.ID ) CountStats OUTER APPLY ( SELECT TOP 1 LatestAlarmLog.StartTime, LatestAlarmLog.EndTime, Channel.Name ChannelName, Meter.Name MeterName FROM Alarm JOIN Series ON Alarm.SeriesID = Series.ID JOIN Channel ON Series.ChannelID = Channel.ID JOIN Meter ON Channel.MeterID = Meter.ID JOIN LatestAlarmLog ON LatestAlarmLog.AlarmID = Alarm.ID WHERE Alarm.AlarmGroupID = AlarmGroup.ID ORDER BY LatestAlarmLog.StartTime DESC, LatestAlarmLog.AlarmLogID DESC ) LastAlarm GO DROP VIEW ActiveAlarmView GO CREATE VIEW ActiveAlarmView AS SELECT Alarm.ID AS AlarmID, Alarm.AlarmGroupID AS AlarmGroupID, AlarmGroup.AlarmTypeID AS AlarmTypeID, AlarmFactor.ID AS AlarmFactorID, AlarmFactor.SeverityID, Alarm.SeriesID AS SeriesID, AlarmFactor.Factor AS Value FROM ( SELECT ID, Factor, AlarmGroupID, SeverityID FROM AlarmFactor UNION SELECT NULL AS ID, 1.0 AS Factor, AlarmGroup.ID AS AlarmGroupID, AlarmGroup.SeverityID FROM AlarmGroup ) AlarmFactor LEFT JOIN Alarm ON AlarmFactor.AlarmGroupID = alarm.AlarmGroupID LEFT JOIN AlarmGroup ON Alarm.AlarmGroupID = AlarmGroup.ID GO CREATE FUNCTION RecursiveMeterSearch(@assetGroupID int) RETURNS TABLE AS RETURN WITH AssetGroupHierarchy AS ( SELECT AssetGroupAssetGroup.ParentAssetGroupID, AssetGroupAssetGroup.ChildAssetGroupID, 1 Depth FROM AssetGroupAssetGroup WHERE AssetGroupAssetGroup.ParentAssetGroupID = @assetGroupID UNION ALL SELECT AssetGroupHierarchy.ParentAssetGroupID, AssetGroupAssetGroup.ChildAssetGroupID, AssetGroupHierarchy.Depth + 1 Depth FROM AssetGroupHierarchy JOIN AssetGroupAssetGroup ON AssetGroupHierarchy.ChildAssetGroupID = AssetGroupAssetGroup.ParentAssetGroupID WHERE AssetGroupHierarchy.Depth < 10 ) SELECT DISTINCT MeterID AS ID FROM MeterAssetGroup LEFT JOIN AssetGroupHierarchy ON MeterAssetGroup.AssetGroupID = AssetGroupHierarchy.ChildAssetGroupID WHERE MeterAssetGroup.AssetGroupID = @assetGroupID OR MeterAssetGroup.AssetGroupID IN (SELECT ChildAssetGroupID FROM AssetGroupHierarchy) GO ALTER TABLE StandardMagDurCurve DROP COLUMN XHigh, XLow, YHigh, YLow, UpperCurve, LowerCurve GO ALTER TABLE StandardMagDurCurve ADD Color VARCHAR(255) NOT NULL DEFAULT('#007a29') GO INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.AdminUser', 'admin', 'admin' WHERE 'Grafana.AdminUser' NOT IN (SELECT Name FROM Setting) GO INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.AuthProxyHeaderName', 'X-WEBAUTH-USER', 'X-WEBAUTH-USER' WHERE 'Grafana.AuthProxyHeaderName' NOT IN (SELECT Name FROM Setting) GO INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.BasePath', 'Grafana', 'Grafana' WHERE 'Grafana.BasePath' NOT IN (SELECT Name FROM Setting) GO INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.HostedURL', 'http://localhost:8185', 'http://localhost:8185' WHERE 'Grafana.HostedURL' NOT IN (SELECT Name FROM Setting) GO INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.InitializationTimeout', '30', '30' WHERE 'Grafana.InitializationTimeout' NOT IN (SELECT Name FROM Setting) GO INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.LastDashboardCookieName', 'x-last-dashboard', 'x-last-dashboard' WHERE 'Grafana.LastDashboardCookieName' NOT IN (SELECT Name FROM Setting) GO INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.OrganizationID', '1', '1' WHERE 'Grafana.OrganizationID' NOT IN (SELECT Name FROM Setting) GO INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.ServerPath', 'Grafana\bin\grafana-server.exe', 'Grafana\bin\grafana-server.exe' WHERE 'Grafana.ServerPath' NOT IN (SELECT Name FROM Setting) GO INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'FileProcessor.FileGroupingPattern', '^(?.*)\.[^\.]*$', '^(?.*)\.[^\.]*$' WHERE 'FileProcessor.FileGroupingPattern' NOT IN (SELECT Name FROM Setting) GO INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'HIDS.HistogramBucket', 'histogram_bucket', 'histogram_bucket' WHERE 'HIDS.HistogramBucket' NOT IN (SELECT Name FROM Setting) GO INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'EventEmail.RestorationURL', 'http://localhost:8989/RestoreEventEmail.cshtml', 'http://localhost:8989/RestoreEventEmail.cshtml' WHERE 'EventEmail.RestorationURL' NOT IN (SELECT Name FROM Setting) GO UPDATE StandardMagDurCurve SET Color = '#007a29' WHERE Name = 'ITIC' GO UPDATE StandardMagDurCurve SET Color = '#edc240' WHERE Name = 'SEMI F47' GO UPDATE StandardMagDurCurve SET Color = '#a30000' WHERE Name = 'IEEE 1668 Type I & II' GO UPDATE StandardMagDurCurve SET Color = '#185aa9' WHERE Name = 'IEEE 1668 Type III' GO UPDATE StandardMagDurCurve SET Color = '#d3d3d3' WHERE Name = 'NERC PRC-024-2' GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Transients', NULL, '#afd8f8') GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Instantaneous Sag', NULL, '#f47d23') GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Instantaneous Swell', NULL, '#008c48') GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Mom. Interruption', NULL, '#ee2e2f') GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Momentary Sag', NULL, '#737373') GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Momentary Swell', NULL, '#662c91') GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temp. Interruption', NULL, '#bd9b33') GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temporary Sag', NULL, '#ff904f') GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temporary Swell', NULL, '#ff9999') GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Sustained Int.', NULL, '#0029A3') GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Undervoltage', NULL, '#cb4b4b') GO INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Overvoltage', NULL, '#4da74d') GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.000001 0, 0.01 0, 0.01 5.0, 0.000001 5.0, 0.000001 0))' WHERE Name = 'IEEE 1159 Transients' GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 0.1, 0.5 0.1, 0.5 0.9, 0.01 0.9, 0.01 0.1))' WHERE Name = 'IEEE 1159 Instantaneous Sag' GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 1.1, 0.5 1.1, 0.5 1.8, 0.01 1.8, 0.01 1.1))' WHERE Name = 'IEEE 1159 Instantaneous Swell' GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 0, 3.0 0, 3.0 0.1, 0.01 0.1, 0.01 0))' WHERE Name = 'IEEE 1159 Mom. Interruption' GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.5 0.1, 3.0 0.1, 3 0.9, 0.5 0.9, 0.5 0.1))' WHERE Name = 'IEEE 1159 Momentary Sag' GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.5 1.1, 3.0 1.1, 3.0 1.4, 0.5 1.4, 0.5 1.1))' WHERE Name = 'IEEE 1159 Momentary Swell' GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 0, 60.0 0, 60.0 0.1, 3.0 0.1, 3.0 0))' WHERE Name = 'IEEE 1159 Temp. Interruption' GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 0.1, 60.0 0.1, 60.0 0.9, 3.0 0.9, 3.0 0.1))' WHERE Name = 'IEEE 1159 Temporary Sag' GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 1.1, 60.0 1.1, 60.0 1.2, 3.0 1.2, 3.0 1.1))' WHERE Name = 'IEEE 1159 Temporary Swell' GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 0, 1000.0 0, 1000.0 0.1, 60.0 0.1, 60.0 0))' WHERE Name = 'IEEE 1159 Sustained Int.' GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 0.8, 1000.0 0.8, 1000.0 0.9, 60.0 0.9, 60.0 0.8))' WHERE Name = 'IEEE 1159 Undervoltage' GO UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 1.1, 1000.0 1.1, 1000.0 1.2, 60.0 1.2, 60.0 1.1))' WHERE Name = 'IEEE 1159 Overvoltage' GO DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Enabled' GO DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Frequency' GO DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Verbose' GO DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Enabled' GO DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Frequency' GO DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Verbose' GO INSERT INTO TriggeredEmailDatasource (Name, AssemblyName, TypeName,ConfigUI) VALUES ('FTT','openXDA.NotificationDataSources.dll','openXDA.NotificationDataSources.FaultTraceTool.FTTDataSource','ftt') GO INSERT INTO AssetType (ID, Name, Description) VALUES (9,'StationAux','Station Auxilary') GO INSERT INTO AssetType (ID, Name, Description) VALUES (10,'StationBattery','Station Battery') GO INSERT INTO AssetType (ID, Name, Description) VALUES (11,'Generation','Generation') GO TRUNCATE TABLE PQMeasurement GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('RMS Voltage', 'RMS Voltage', 'Volts', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'RMS')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Unbalance(S2/S1)', 'Voltage Unbalance(S2/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Unbalance(S0/S1)', 'Voltage Unbalance(S0/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Flicker Pst', 'Short Term Flicker Perceptibility (Pst)', 'Per Unit', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'FlkrPST')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage THD', 'Voltage THD', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Harmonic', 'Voltage Harmonic', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Unbalance(S2/S1)', 'Current Unbalance(S2/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Unbalance(S0/S1)', 'Current Unbalance(S0/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current THD', 'Current THD', 'Amps', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Harmonic', 'Current Harmonic', 'Amps', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Active Power', 'Active Power', 'KW', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'P')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Reactive Power', 'Reactive Power', 'KVAR', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'Q')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Apparent Power', 'Apparent Power', 'KVA', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S')) GO INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Power Factor', 'Power Factor', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'PF')) GO INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) SELECT 'FAWG.Enabled', 'False', 'False' WHERE 'FAWG.Enabled' NOT IN (SELECT Name FROM [SystemCenter.Setting]) GO CREATE Table [ExternalDatabases] ( ID int IDENTITY(1,1) NOT NULL PRIMARY KEY, Name varchar(200) NOT NULL, Schedule varchar(50) NULL DEFAULT(NULL), ConnectionString Varchar(MAX) NOT NULL, DataProviderString VARCHAR(MAX) NULL, Encrypt bit NOT NULL DEFAULT(0), LastDataUpdate DATETIME2 NULL DEFAULT(NULL), Constraint UC_ExternalDatabase UNIQUE(Name) ) ALTER TABLE ExternalOpenXDAField ADD ExternalDBTableID INT NOT NULL FOREIGN KEY REFERENCES [extDBTables](ID) GO ALTER TABLE LocationDrawing ADD Number VARCHAR(200) NULL, Category VARCHAR(max) NULL, CONSTRAINT UC_SystemCenter_LocationDrawing_Number UNIQUE (Number) GO INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) SELECT 'MiMD.Url', 'http://localhost:8989', '' WHERE 'MiMD.Url' NOT IN (SELECT Name FROM [SystemCenter.Setting]) GO INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) SELECT 'MiMD.APIKey', '', '' WHERE 'MiMD.APIKey' NOT IN (SELECT Name FROM [SystemCenter.Setting]) GO INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) SELECT 'MiMD.APIToken', '', '' WHERE 'MiMD.APIToken' NOT IN (SELECT Name FROM [SystemCenter.Setting]) GO CREATE TRIGGER [dbo].[UO_AdditonaFieldValue] ON [dbo].[AdditionalFieldValue] AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE original SET UpdatedOn = SYSDATETIME() FROM dbo.[AdditionalFieldValue] as original INNER JOIN deleted ON original.ID = deleted.ID AND ( original.Value <> deleted.Value OR (original.Value IS NULL AND deleted.Value IS NOT NULL) OR (original.Value IS NOT NULL AND deleted.Value IS NULL) ); END GO INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) SELECT 'PQBrowser.Url', 'http://localhost', 'http://localhost' WHERE 'PQBrowser.Url' NOT IN (SELECT Name FROM [SystemCenter.Setting]) GO ALTER TABLE [SEBrowser.Widget] DROP COLUMN Enabled GO ALTER TABLE [SEBrowser.Widget] ADD Type VARCHAR(max) NOT NULL DEFAULT('OpenSEE') GO UPDATE [SEBrowser.Widget] SET Name = 'OpenSEE' WHERE Name = 'EventSearchOpenSEE' GO UPDATE [SEBrowser.Widget] SET Type = 'OpenSEE' WHERE Name = 'OpenSEE' GO UPDATE [SEBrowser.Widget] SET Name = 'FaultSegments' WHERE Name = 'EventSearchFaultSegments' GO UPDATE [SEBrowser.Widget] SET Type = 'EventSearchFaultSegments' WHERE Name = 'FaultSegments' GO UPDATE [SEBrowser.Widget] SET Name = 'VoltageDisturbances' WHERE Name = 'EventSearchAssetVoltageDisturbances' GO UPDATE [SEBrowser.Widget] SET Type = 'VoltageDisturbances' WHERE Name = 'VoltageDisturbances' GO UPDATE [SEBrowser.Widget] SET Name = 'ESRIMap' WHERE Name = 'TVAESRIMap' GO UPDATE [SEBrowser.Widget] SET Type = 'ESRIMap' WHERE Name = 'ESRIMap' GO UPDATE [SEBrowser.Widget] SET Name = 'FaultInfo' WHERE Name = 'TVAFaultInfo' GO UPDATE [SEBrowser.Widget] SET Type = 'FaultInfo' WHERE Name = 'FaultInfo' GO UPDATE [SEBrowser.Widget] SET Type = 'LineParameters' WHERE Name = 'LineParameters' GO UPDATE [SEBrowser.Widget] SET Name = 'Lightning' WHERE Name = 'TVALightning' GO UPDATE [SEBrowser.Widget] SET Type = 'Lightning' WHERE Name = 'Lightning' GO UPDATE [SEBrowser.Widget] SET Name = 'StructureInfo' WHERE Name = 'TVAStructureInfo' GO UPDATE [SEBrowser.Widget] SET Type = 'StructureInfo' WHERE Name = 'StructureInfo' GO UPDATE [SEBrowser.Widget] SET Type = 'AssetHistoryTable' WHERE Name = 'AssetHistoryTable' GO UPDATE [SEBrowser.Widget] SET Type = 'AssetHistoryStats' WHERE Name = 'AssetHistoryStats' GO UPDATE [SEBrowser.Widget] SET Name = 'CorrelatedSags' WHERE Name = 'EventSearchCorrelatedSags' GO UPDATE [SEBrowser.Widget] SET Type = 'CorrelatedSags' WHERE Name = 'CorrelatedSags' GO UPDATE [SEBrowser.Widget] SET Type = 'TVASIDA' WHERE Name = 'TVASIDA' GO UPDATE [SEBrowser.Widget] SET Name = 'SOE' WHERE Name = 'TVASOE' GO UPDATE [SEBrowser.Widget] SET Type = 'SOE' WHERE Name = 'SOE' GO UPDATE [SEBrowser.Widget] SET Type = 'TVASLC' WHERE Name = 'TVASLC' GO UPDATE [SEBrowser.Widget] SET Type = 'TVAPQWeb' WHERE Name = 'TVAPQWeb' GO UPDATE [SEBrowser.Widget] SET Name = 'InterruptionReport' WHERE Name = 'HECCOIR' GO UPDATE [SEBrowser.Widget] SET Type = 'InterruptionReport' WHERE Name = 'InterruptionReport' GO UPDATE [SEBrowser.Widget] SET Type = 'pqi' WHERE Name = 'pqi' GO UPDATE [SEBrowser.Widget] SET Name = 'FileInfo' WHERE Name = 'EventSearchFileInfo' GO UPDATE [SEBrowser.Widget] SET Type = 'FileInfo' WHERE Name = 'FileInfo' GO UPDATE [SEBrowser.Widget] SET Name = 'Notes' WHERE Name = 'EventSearchNoteWindow' GO UPDATE [SEBrowser.Widget] SET Type = 'Notes' WHERE Name = 'Notes' GO UPDATE [SEBrowser.Widget] SET Name = 'RelayPerformance' WHERE Name = 'EventSearchRelayPerformance' GO UPDATE [SEBrowser.Widget] SET Type = 'RelayPerformance' WHERE Name = 'RelayPerformance' GO UPDATE [SEBrowser.Widget] SET Name = 'BreakerPerformance' WHERE Name = 'EventSearchBreakerPerformance' GO UPDATE [SEBrowser.Widget] SET Type = 'BreakerPerformance' WHERE Name = 'BreakerPerformance' GO UPDATE [SEBrowser.Widget] SET Name = 'CapBankAnalyticOverview' WHERE Name = 'EventSearchCapBankAnalyticOverview' GO UPDATE [SEBrowser.Widget] SET Type = 'CapBankAnalyticOverview' WHERE Name = 'CapBankAnalyticOverview' GO